﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using NetSurveillance.WebApp.DataServices.Models;
using ServiceStack.OrmLite;

namespace NetSurveillance.WebApp.DataServices
{
    internal class DataInit
    {
        public static void InitData(DataProvider dataProvider)
        {
            InitUser(dataProvider);
            InitProtocols(dataProvider);
            InitMonitorSetting(dataProvider);
            InitAlertSetting(dataProvider);
        }

        public static void InitUser(DataProvider dataProvider)
        {
            IList<User> users = new List<User>();
            for (int i = 0; i < 50; i++)
            {
                users.Add(new User
                    {
                        UserName = string.Format("user{0:000}", i),
                        RealName = string.Format("user{0:000}", i),
                        Password = "123456",
                        Email = string.Format("user{0:000}@163.com", i),
                        Phone = "13800138000",
                        UserType = UserType.Normal
                    });
            }
            dataProvider.UserService.SaveAll(users);
            var adminUser = new User
                {
                    UserName = "admin",
                    RealName = "admin",
                    Password = "123456",
                    Email = "admin@admin.com",
                    UserType = UserType.Administrator
                };
            dataProvider.UserService.Save(adminUser);
            dataProvider.UserService.SaveAcctPermission(adminUser.Id, PermissionType.Delete);
        }

        public static void InitProtocols(DataProvider dataProvider)
        {
            string[] protocols = new[] {"http", "ftp", "pop3", "smtp", "qq"};
            dataProvider.ProtocolsService.SaveAllProtocols(protocols);
        }

        public static void InitMonitorSetting(DataProvider dataProvider)
        {
            var monitorSt = new MonitorSetting
                {
                    IsSaveWebContent = false,
                    MinSize = 0,
                    MaxSize = 0,
                    SaveFileExts = "htm,html",
                    SaveFileDay = 0,
                    DefaultPageSize = 10,
                    IsShowTitleRecord = true
                };
            dataProvider.MonitorSettingService.Save(monitorSt);
        }

        public static void InitAlertSetting(DataProvider dataProvider)
        {
            var smsAlertSt = new AlertSetting {AlertType = 1, StatusToken = 0};
            var emailAlertSt = new AlertSetting {AlertType = 2, StatusToken = 0};
            dataProvider.AlertSettingService.SaveAll(new List<AlertSetting> {smsAlertSt, emailAlertSt});
        }

        /// <summary>
        /// 初始化sql函数
        /// 执行此函数前请先确保已经执行过以下命令
        /// createlang -d dbName -h 127.0.0.1 -U postgres plpgsql
        /// </summary>
        /// <param name="dbConn">数据库连接，该连接需在using中</param>
        public static void InitFunction(IDbConnection dbConn)
        {
            InitPlpgsql(dbConn);
            const string getSensAccIdFunSql =
                @"create or replace function getSensAcctId(account character varying(300),
                    password character varying(300),taskId bigint)
                    returns bigint language plpgsql as $$
                        declare sensId bigint;
                        begin
                        select into sensId ""Id"" from ""SensitiveAccounts""
                            where ""Account""=account and ""Password""=password and ""TaskId""=taskId;
                        if not found then
                        insert into ""SensitiveAccounts""(""TaskId"",""Account"",""Password"") values(taskId,account,password) returning into sensId ""Id"";
                        else
                        update ""SensitiveAccounts"" set ""StatusToken""=1,""LastModifiedTime""=now() where ""Id""=sensId;
                        end if;
                        return sensId;
                        end
                    $$;";
            const string saveQQPassFunSql =
                @"create or replace function saveQQPass(acct character varying(64),pass character varying(64))
                    returns bigint language plpgsql as $$
                    declare qqPassId bigint;
                    begin
                        select into qqPassId ""Id"" from ""QQPasswords"" where ""Account""=acct;
                        if not found then
                        insert into ""QQPasswords""(""Account"",""Password"") values(acct,pass) returning into qqPassId ""Id"";
                        else
                        update ""QQPasswords"" set ""Password""=pass,""StatusToken""=1,""LastModifiedTime""=now() where ""Id""=qqPassId;
                        end if;
                        return qqPassId;
                    end
                    $$;";
            const string saveHostFunSql =
                @"create or replace function saveHost(ip_addr character varying(32),
                    mac_addr character varying(32),hostname character varying(32),taskId bigint)
                    returns bigint language plpgsql as $$
                    declare hostId bigint;
                    begin
                        select into hostId ""Id"" from ""Hosts""
                            where ""Mac""=mac_addr and ""IP""=ip_addr and ""TaskId""=taskId;
                        if not found then
                        insert into ""Hosts""(""HostName"",""IP"",""Mac"",""TaskId"") values(hostname,ip_addr,mac_addr,taskId) returning into hostId ""Id"";
                        else
                        update ""Hosts"" set ""StatusToken""=1,""LastModifiedTime""=now() where ""Id""=hostId;
                        end if;
                        return hostId;
                    end
                    $$;";
            dbConn.ExecuteSql(getSensAccIdFunSql);
            dbConn.ExecuteSql(saveQQPassFunSql);
            dbConn.ExecuteSql(saveHostFunSql);
        }

        /// <summary>
        /// 创建plpgsql语言
        /// </summary>
        /// <param name="dbConn">数据库连接，该连接需在using中</param>
        public static void InitPlpgsql(IDbConnection dbConn)
        {
            const string initSql =
                @"create or replace function initPlpgsql()
                    returns void language sql as $$
                        create language 'plpgsql';
                    $$;
                select case when exists(select 1 from pg_catalog.pg_language where lanname='plpgsql')
                    then null else initPlpgsql() end;
                drop function initPlpgsql();";
            dbConn.ExecuteSql(initSql);
        }
    }
}